package com.link.base.base.system.service;

import com.alibaba.fastjson.JSONArray;
import com.link.base.base.system.dao.mybatis.mapper.ConfigDbMapper;
import com.link.base.base.system.model.ConfigDb;
import com.link.base.base.system.utils.JdbcUtil;
import com.link.core.basic.dao.mapper.BasicMapper;
import com.link.core.basic.query.Filter;
import com.link.core.basic.service.BasicServiceImpl;
import com.link.core.util.StringUtils;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;

/**
 * 查询系统数据库表（仅包含查询功能）
 *
 * @author LiYang
 * @date 20190820
 */
@Service
public class ConfigDbServiceImpl extends BasicServiceImpl<ConfigDb> implements ConfigDbService {

    @Resource
    public ConfigDbMapper configDbMapper;

    @Override
    public BasicMapper<ConfigDb> getBasicMapper() throws Exception {
        return configDbMapper;
    }

    @Override
    public List<Map<String, Object>> queryMySqlTable(ConfigDb configDb) throws Exception {
        JdbcUtil.changeDbConfig(configDb);
        StringBuilder sb = new StringBuilder("");
        sb.append("SELECT\n");
        sb.append("t1.table_name tableName,\n");
        sb.append("t1.create_time createTime,\n");
        sb.append("t1.update_time updateTime,\n");
        sb.append("t1.table_comment tableComment,\n");
        sb.append("t1.table_type tableType \n");
        sb.append("from\n");
        sb.append("information_schema.TABLES t1 \n");
        sb.append("where\n");
        sb.append("1 = 1 \n");
        sb.append("and t1.TABLE_SCHEMA = '");
        sb.append(StringUtils.escapeString(configDb.getDbName(), "="));
        sb.append("' \n");
        //如果模糊查询条件不为空，则添加模糊查询条件
        if (!StringUtils.isBlank(configDb.getFuzzyCondition())) {
            sb.append("and (t1.table_name like '%");
            sb.append(StringUtils.escapeString(configDb.getFuzzyCondition(), Filter.OP_LIKE));
            sb.append("%' or t1.table_comment like '%");
            sb.append(StringUtils.escapeString(configDb.getFuzzyCondition(), Filter.OP_LIKE));
            sb.append("%')");
        }
        System.out.println(sb.toString());
        List<Map<String, Object>> result = JdbcUtil.getQueryResult(configDb, sb.toString());
        return result;
    }

    @Override
    public List<Map<String, Object>> queryMySqlTableColumn(ConfigDb configDb) throws Exception {
        JdbcUtil.changeDbConfig(configDb);
        StringBuilder sb = new StringBuilder("");
        sb.append("SELECT\n");
        sb.append("t1.table_name tableName,\n");
        sb.append("t1.column_name columnName,\n");
        sb.append("t1.data_type dataType,\n");
        sb.append("t1.CHARACTER_MAXIMUM_LENGTH characterMaxLength,\n");
        sb.append("t1.column_comment columnComment, \n");
        sb.append("t2.create_time createTime,\n");
        sb.append("t2.update_time updateTime,\n");
        sb.append("t2.table_comment tableComment,\n");
        sb.append("t2.table_type tableType \n");
        sb.append("FROM\n");
        sb.append("information_schema.COLUMNS t1 \n");
        sb.append("left join information_schema.TABLES t2 on t1.table_name = t2.table_name \n");
        sb.append("WHERE\n");
        sb.append("1 = 1 \n");
        sb.append("and t1.TABLE_SCHEMA = '");
        sb.append(configDb.getDbName());
        sb.append("' \n");
        if (StringUtils.isBlank(configDb.getFuzzyCondition())) {
            if (!StringUtils.isBlank(configDb.getTableName())) {
                sb.append("and t1.table_name = '");
                sb.append(configDb.getTableName());
                sb.append("'");
            }
        } else {
            sb.append("and (t1.column_name like '%");
            sb.append(StringUtils.escapeString(configDb.getFuzzyCondition(), Filter.OP_LIKE));
            sb.append("%' or t1.column_comment like '%");
            sb.append(StringUtils.escapeString(configDb.getFuzzyCondition(), Filter.OP_LIKE));
            sb.append("%')");
        }
        System.out.println(sb.toString());
        List<Map<String, Object>> result = JdbcUtil.getQueryResult(configDb, sb.toString());
        return result;
    }

    @Override
    public List<Map<String, Object>> fuzzyQueryMySql(ConfigDb configDb) throws Exception {
        JdbcUtil.changeDbConfig(configDb);
        //得到模糊查询的表
        List<Map<String, Object>> searchTableResult = this.queryMySqlTable(configDb);
        //得到模糊查询的列
        List<Map<String, Object>> searchColumnResult = this.queryMySqlTableColumn(configDb);
        //声明返回的结果对象
        List<Map<String, Object>> result = new ArrayList<>(64);
        //声明一个table map，用于临时存放表信息
        Map<String, Map<String, Object>> tableInfo = new HashMap<>(64);
        for (Map<String, Object> tableRecord : searchTableResult) {
            tableInfo.put((String) tableRecord.get("TABLE_NAME"), tableRecord);
        }
        //声明一个list，用于存放最后模糊匹配到的结果对象
        for (Map<String, Object> columnRecord : searchColumnResult) {
            //当前列记录对应的表名
            String tableName = (String) columnRecord.get("TABLE_NAME");
            //尝试从临时表集合中取出该表
            Map<String, Object> tableInfoTemp = tableInfo.get(tableName);
            if (null == tableInfoTemp) {
                //如果不存在将该结果重构存入临时表集合
                JSONArray children = new JSONArray();
                //将列信息添加到表信息中
                children.add(columnRecord);
                tableInfoTemp = new HashMap<>(4);
                tableInfoTemp.put("TABLE_NAME",columnRecord.get("TABLE_NAME"));
                tableInfoTemp.put("CREATE_TIME",columnRecord.get("CREATE_TIME"));
                tableInfoTemp.put("UPDATE_TIME",columnRecord.get("UPDATE_TIME"));
                tableInfoTemp.put("TABLE_COMMENT",columnRecord.get("TABLE_COMMENT"));
                tableInfoTemp.put("TABLE_TYPE",columnRecord.get("TABLE_TYPE"));
                //在表对象中声明一个成员“CHILDREN”，用来存放包含的列信息
                tableInfoTemp.put("CHILDREN", children);
                tableInfo.put(tableName, tableInfoTemp);
            } else {
                //如果存在则重构列记录，存入临时表集合
                JSONArray children = (JSONArray) tableInfoTemp.get("CHILDREN");
                if (null == children) {
                    children = new JSONArray();
                }
                children.add(columnRecord);
                tableInfoTemp.put("CHILDREN", children);
                tableInfo.put(tableName, tableInfoTemp);
            }
        }
        Set<String> keySet = tableInfo.keySet();
        Iterator<String> iter = keySet.iterator();
        //循环临时表信息，将其成员封装为一个list返回
        while (iter.hasNext()) {
            result.add(tableInfo.get(iter.next()));
        }
        return result;
    }
}
